package com.the_last.utils;

import cn.idev.excel.FastExcel;
import cn.idev.excel.read.listener.ReadListener;
import jakarta.annotation.Resource;
import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.net.URLEncoder;
import java.util.Collection;
import java.util.List;
import java.util.concurrent.ExecutorService;

@Component
public class ExcelUtil {
    public final String EXCEL_FORMAT_PATTERN_ERROR = "文件格式错误，请上传xls或xlsx格式的文件";
    
    @Resource
    private ExecutorService executorService;
    
    /**
     * 验证Excel文件格式
     */
    public boolean verifyExcel(String fileName) {
        return FileUtil.verifyFile(fileName, FileUtil.EXCEL_FORMAT_PATTERN);
    }
    
    /**
     * 验证文件大小
     */
    public boolean verifyFileSize(MultipartFile file, long maxSizeInMB) {
        return file.getSize() <= maxSizeInMB * 1024 * 1024;
    }
    
    /**
     * 写入到本地文件
     */
    public void writeToLocal(
            String fileName,
            Class<?> clazz,
            String sheetName,
            Collection<?> collection
    ) {
        FastExcel.write(fileName + ".xlsx", clazz).sheet(sheetName).doWrite(collection);
    }
    
    /**
     * 从本地文件读取（同步）
     */
    public <T> Collection<T> readFromLocal(
            String fileName,
            Class<T> clazz
    ) {
        if (!FileUtil.verifyFile(fileName, FileUtil.EXCEL_FORMAT_PATTERN)) {
            throw new RuntimeException(EXCEL_FORMAT_PATTERN_ERROR);
        }
        return FastExcel.read(fileName).head(clazz).sheet().doReadSync();
    }
    
    /**
     * 写入Excel到HTTP响应
     *
     * @param fileName Excel文件名（不含扩展名）
     * @param sheetName 工作表名称
     * @param tClass 数据类型Class
     * @param collection 数据集合
     * @param response HTTP响应对象
     * @param writeToLocal 是否同时保存到本地
     */
    public <T> void writeToResponse(
            String fileName,
            String sheetName,
            Class<T> tClass,
            Collection<T> collection,
            HttpServletResponse response,
            boolean writeToLocal
    ) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        FastExcel.write(response.getOutputStream(), tClass).sheet(sheetName).doWrite(collection);
        String finalFileName = fileName;
        // 根据需要，保存到本地
        if (writeToLocal) {
            executorService.execute(() -> {
                writeToLocal(finalFileName, tClass, sheetName, collection);
            });
        }
    }
    
    /**
     * 写入Excel到HTTP响应（不保存本地）
     */
    public <T> void writeToResponse(
            String fileName,
            String sheetName,
            Class<T> tClass,
            Collection<T> collection,
            HttpServletResponse response
    ) throws IOException {
        writeToResponse(fileName, sheetName, tClass, collection, response, false);
    }
    
    /**
     * 读取上传的Excel文件（同步）
     */
    public <T> Collection<T> readMultipartFile(
            MultipartFile file,
            Class<T> tClass
    ) throws IOException {
        String originalFilename = file.getOriginalFilename();
        if (!FileUtil.verifyFile(originalFilename, FileUtil.EXCEL_FORMAT_PATTERN)) {
            throw new RuntimeException(EXCEL_FORMAT_PATTERN_ERROR);
        }
        return FastExcel.read(file.getInputStream()).head(tClass).sheet().doReadSync();
    }
    
    /**
     * 读取上传的Excel文件（异步，使用监听器）
     */
    public <T> void readMultipartFileAsync(
            MultipartFile file,
            Class<T> tClass,
            ReadListener<T> readListener
    ) throws IOException {
        String originalFilename = file.getOriginalFilename();
        if (!FileUtil.verifyFile(originalFilename, FileUtil.EXCEL_FORMAT_PATTERN)) {
            throw new RuntimeException(EXCEL_FORMAT_PATTERN_ERROR);
        }
        FastExcel.read(file.getInputStream(), tClass, readListener).sheet().doRead();
    }
    
    /**
     * 生成Excel模板到字节数组
     */
    public <T> byte[] generateTemplate(
            Class<T> tClass,
            String sheetName,
            Collection<T> sampleData
    ) throws IOException {
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        FastExcel.write(outputStream, tClass).sheet(sheetName).doWrite(sampleData);
        return outputStream.toByteArray();
    }
    
    /**
     * 生成空的Excel模板到字节数组
     */
    public <T> byte[] generateEmptyTemplate(
            Class<T> tClass,
            String sheetName
    ) throws IOException {
        return generateTemplate(tClass, sheetName, java.util.Collections.emptyList());
    }
    
    /**
     * 生成带下拉选项的Excel模板
     * 
     * @param tClass 数据类型Class
     * @param sheetName 工作表名称
     * @param sampleData 示例数据
     * @param dropdownColumnIndex 下拉选项列索引（从0开始）
     * @param dropdownOptions 下拉选项值列表
     * @param dataStartRow 数据开始行（从0开始，通常是1，因为0是标题行）
     * @param maxRows 下拉选项应用的最大行数
     * @return Excel文件字节数组
     */
    public <T> byte[] generateTemplateWithDropdown(
            Class<T> tClass,
            String sheetName,
            Collection<T> sampleData,
            int dropdownColumnIndex,
            List<String> dropdownOptions,
            int dataStartRow,
            int maxRows
    ) throws IOException {
        // 先用FastExcel生成基础模板
        ByteArrayOutputStream tempOutput = new ByteArrayOutputStream();
        FastExcel.write(tempOutput, tClass).sheet(sheetName).doWrite(sampleData);
        
        // 使用POI添加下拉选项
        try (XSSFWorkbook workbook = new XSSFWorkbook(new java.io.ByteArrayInputStream(tempOutput.toByteArray()))) {
            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                sheet = workbook.getSheetAt(0); // 如果按名称找不到，使用第一个sheet
            }
            
            // 创建数据验证助手
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            
            // 创建下拉选项约束
            DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(
                dropdownOptions.toArray(new String[0])
            );
            
            // 设置下拉选项应用的单元格范围
            CellRangeAddressList addressList = new CellRangeAddressList(
                dataStartRow, dataStartRow + maxRows - 1, // 行范围
                dropdownColumnIndex, dropdownColumnIndex  // 列范围
            );
            
            // 创建数据验证对象
            DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
            validation.setShowErrorBox(true);
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.createErrorBox("输入错误", "请从下拉列表中选择一个有效的商品分类");
            validation.setSuppressDropDownArrow(false);
            
            // 添加数据验证到工作表
            sheet.addValidationData(validation);
            
            // 输出到字节数组
            ByteArrayOutputStream finalOutput = new ByteArrayOutputStream();
            workbook.write(finalOutput);
            return finalOutput.toByteArray();
        }
    }
    
    /**
     * 生成带下拉选项的Excel模板到HTTP响应
     */
    public <T> void writeTemplateWithDropdownToResponse(
            String fileName,
            String sheetName,
            Class<T> tClass,
            Collection<T> sampleData,
            int dropdownColumnIndex,
            List<String> dropdownOptions,
            HttpServletResponse response
    ) throws IOException {
        byte[] templateData = generateTemplateWithDropdown(
            tClass, sheetName, sampleData, 
            dropdownColumnIndex, dropdownOptions, 
            1, 1000 // 数据从第2行开始，支持1000行数据
        );
        
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        
        response.getOutputStream().write(templateData);
        response.getOutputStream().flush();
    }
    
    /**
     * 下载现有的Excel模板文件
     */
    public void downloadExcelTemplate(String templateFileName, String downloadFileName, HttpServletResponse response) throws IOException {
        // 首先尝试从项目根目录读取模板文件
        File templateFile = new File(templateFileName);
        InputStream inputStream = null;
        
        try {
            if (templateFile.exists()) {
                // 从项目根目录读取
                inputStream = new FileInputStream(templateFile);
            } else {
                // 尝试从classpath读取
                ClassPathResource resource = new ClassPathResource(templateFileName);
                if (resource.exists()) {
                    inputStream = resource.getInputStream();
                } else {
                    throw new IOException("模板文件不存在: " + templateFileName);
                }
            }
            
            // 设置响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            String encodedFileName = URLEncoder.encode(downloadFileName, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + encodedFileName + ".xlsx");
            
            // 将模板文件内容写入响应
            byte[] buffer = new byte[1024];
            int length;
            while ((length = inputStream.read(buffer)) > 0) {
                response.getOutputStream().write(buffer, 0, length);
            }
            response.getOutputStream().flush();
            
        } finally {
            if (inputStream != null) {
                inputStream.close();
            }
        }
    }
}
